package org.example.demo.service.impl;

import com.alibaba.excel.EasyExcel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.demo.entity.Student;
import org.example.demo.listen.StudentExcelListener;
import org.example.demo.mapper.StudentMapper;
import org.example.demo.service.StudentService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author lining
 * @since 2023-11-24
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {
    @Autowired
    private  StudentService studentService;

    @Override
    public void saveExcel(MultipartFile file ,StudentService studentService){
        try{
            InputStream in=file.getInputStream();
            EasyExcel
                    .read(
                            in,
                            Student.class,
                            new StudentExcelListener(studentService)
                    ).sheet()
                    .doRead();
        }catch (Exception e){}

    }
    private static final String DB_URL = "jdbc:mysql://localhost:3306/sdxy";
    private static final String DB_USERNAME = "root";
    private static final String DB_PASSWORD = "";
    public   byte[] exportToExcel(){
      try (Connection conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
           Statement stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery("SELECT * FROM student")) {

          ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
          Workbook workbook = new XSSFWorkbook();
          Sheet sheet = workbook.createSheet("Sheet1");

          // 写入表头
          ResultSetMetaData metaData = rs.getMetaData();
          int columnCount = metaData.getColumnCount();
          Row headerRow = sheet.createRow(0);
          for (int i = 1; i <= columnCount; i++) {
              String columnName = metaData.getColumnName(i);
              Cell cell = headerRow.createCell(i - 1);
              cell.setCellValue(columnName);
          }

          // 写入数据行
          int rowNum = 1;
          while (rs.next()) {
              Row dataRow = sheet.createRow(rowNum++);
              for (int i = 1; i <= columnCount; i++) {
                  Cell cell = dataRow.createCell(i - 1);
                  cell.setCellValue(rs.getString(i));
              }
          }

          workbook.write(outputStream);
          workbook.close();

          return outputStream.toByteArray();
      } catch (SQLException | IOException e) {
          e.printStackTrace();
          return null;
      }
    }

//   public  void importData(MultipartFile file) throws IOException {
//       List<Student> dataList = null;
//       try {
//           dataList = ExcelUtil.parseExcel(file);
//       } catch (InvalidFormatException e) {
//           throw new RuntimeException(e);
//       }
//
//       // 将数据保存到数据库
//       studentService.save(dataList);
//   }

    public void importExcelData(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0); // 假设Excel文件中只有一个Sheet

            // 遍历行
            for (Row row : sheet) {
                Cell cell1=row.getCell(0);
                cell1.setCellType(CellType.NUMERIC);
                // 读取单元格数据
                int cell12 = (int) cell1.getNumericCellValue();
                String cell2 = row.getCell(1).getStringCellValue();

                // ...

                // 创建实体对象并设置属性
               Student entity = new Student();
                entity.setId(cell12);
                entity.setName(cell2);
                // ...

                // 将实体对象保存到数据库
                studentService.save(entity);
            }

            workbook.close();
            inputStream.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
            // 处理异常
        }
    }





}
